from odoo import fields, models, _, api
from datetime import timedelta, datetime


class ProductQuantReportWizard(models.TransientModel):
    _name = 'product.quant.report.wizard'
    _description = 'Product Quant Report Wizard'

    def get_per_day(self):
        today = datetime.today()
        if today.month == 1:
            last_month_today = today.replace(year=today.year - 1, month=12, day=today.day)
        else:
            try:
                last_month_today = today.replace(month=today.month - 1, day=today.day)
            except ValueError:
                # 如果出现ValueError，说明上个月没有今天的日期，比如从4月31日到3月31日或2月29日
                # 这时我们可以将日期设置为上个月的最后一天
                last_month_today = (today.replace(day=1) - timedelta(days=1))
        return last_month_today

    def get_today(self):
        return datetime.today()

    start_date = fields.Date(string='Start Date', required=True, default=lambda self: self.get_per_day())
    end_date = fields.Date(string='End Date', required=True, default=lambda self: self.get_today())

    def action_report(self):
        """
        处理时间问题
        """
        bs_type = self.env.context.get('bs_type')
        start_date = self.start_date.strftime('%Y-%m-%d')
        end_date = self.end_date.strftime('%Y-%m-%d')
        f1 = datetime(year=self.start_date.year, month=self.start_date.month, day=self.start_date.day)
        action = {
            'view_mode': 'tree',
            'type': 'ir.actions.act_window',
            'context': {
                'from_date': (f1 - timedelta(hours=7)).strftime('%Y-%m-%d %H:%M:%S'),
                'to_date': self.end_date.strftime('%Y-%m-%d 16:59:59')
            },
        }
        if bs_type == 'material':
            view_id = self.env.ref('sb_stock_inherit.product_quant_report_tree').id
            action.update({
                'name': _('Product Quant Report %s - %s') % (start_date, end_date),
                'res_model': 'product.quant.report',
                'view_id': view_id,
            })
            return action
        elif bs_type == 'finished':
            view_id = self.env.ref('sb_stock_inherit.finished_quant_report_tree').id
            action.update({
                'name': _('Finished Quant Report %s - %s') % (start_date, end_date),
                'res_model': 'finished.quant.report',
                'view_id': view_id,
            })
            return action
        elif bs_type == 'production':
            view_id = self.env.ref('sb_stock_inherit.finished_production_report_tree').id
            action.update({
                'name': _('Report %s - %s') % (start_date, end_date),
                'res_model': 'finished.production.report',
                'view_id': view_id,
            })
            return action


class ProductQuantReport(models.Model):
    _name = 'product.quant.report'
    _description = 'Product Quant Report'
    _auto = False

    purchase_ids = fields.Many2many('purchase.order', string='Purchase Order', compute='_compute_quant_fields')  # 采购单
    supplier_ids = fields.Many2many('res.partner', string='Supplier', compute='_compute_quant_fields')  # 供应商
    supplier_code = fields.Char(string="supplier code", compute='_compute_quant_fields')  # 供应商编码
    s_supplier_name_en = fields.Char(string='Name EN', compute='_compute_quant_fields')  # 供应商英文名
    picking_id = fields.Many2one('stock.picking', string='Picking')  # 调拨单
    reference = fields.Char(string='Reference')  # 调拨单号
    apply_id = fields.Many2one('work.station', string='work station', related='picking_id.work_station_id')  # 领用工序
    product_id = fields.Many2one('product.product', string='Product')  # 产品
    default_code = fields.Char(related='product_id.default_code', string='Product Code')  # 产品编码
    department_id = fields.Many2one('hr.department', string='Department',
                                    related='picking_id.apply_id.department_id')  # 部门
    bs_type = fields.Char(string='BS Type')  # 业务类型
    date = fields.Datetime(string='oper Date')  # 操作时间
    location_id = fields.Many2one('stock.location', string='From')  # 原位置
    location_dest_id = fields.Many2one('stock.location', string='To')  # 目的位置
    lot_id = fields.Many2one('stock.lot', string='Lot')  # 批次
    purchase_qty = fields.Float(string='Purchase Quantity', compute='_compute_quant_fields')  # 采购数量
    product_uom = fields.Many2one('uom.uom', string='Purchase Uom', compute='_compute_quant_fields')  # 采购单位
    s_purchase_qty = fields.Float(string='S Quantity', compute='_compute_quant_fields')  # (出/入)库数 (pp,干膜)
    s_product_uom = fields.Many2one('uom.uom', string='S Uom',
                                    compute='_compute_quant_fields')  # (出/入)库单位 (pp,干膜)
    quantity = fields.Float(string='S Quantity')  # (出/入)库数
    uom_id = fields.Many2one('uom.uom', related='product_id.uom_id', string='S Uom')  # (出/入)库单位
    price_unit = fields.Float(string='Purchase Unit', compute='_compute_quant_fields')  # 采购单价
    price_total = fields.Float(string='Purchase Total', compute='_compute_quant_fields')  # 采购总价
    price = fields.Float(string='Move Price', compute='_compute_quant_fields')  # 库存单价
    price_subtotal = fields.Float(string='Price Subtotal', compute='_compute_quant_fields')  # 库存总价
    currency_id = fields.Many2one('res.currency', string='Currency', compute='_compute_quant_fields')  # 币种
    user_ids = fields.Many2many('res.users', string='Users', compute='_compute_quant_fields')  # 销售员用户
    country_ids = fields.Many2many('res.country', string='Country', compute='_compute_quant_fields')  # 国家
    taxes_ids = fields.Many2many('account.tax', string='Taxes', compute='_compute_quant_fields')  # 税项

    move_id = fields.Many2one('stock.move.line', string='Move Line')  # 移动
    type = fields.Selection([('in', 'In'), ('out', 'Out')], string='Type')  # 出入库类型

    def action_report_at_date(self):
        from_date = self.env.context.get('from_date')
        to_date = self.env.context.get('to_date')
        sb_type = self.env.context.get('sb_type')
        return {
            'type': 'ir.actions.act_url',
            'url': f'/download/sb/product/quant/excel?from_date={from_date}&to_date={to_date}&tp={sb_type}',
            'target': 'new',
        }

    @api.depends('product_id', 'quantity')
    def _compute_quant_fields(self):
        """
        库存数量转采购数量, 统计采购总额
        price_unit,
        product_uom,
        currency_id,
        supplier_id
        """
        for record in self:
            purchase_line = record.move_id.move_id.purchase_line_id
            record.purchase_ids = purchase_line.order_id.ids
            price_unit = purchase_line.price_unit
            product_uom = record.product_id.uom_po_id.id
            currency_id = purchase_line.currency_id.id
            if not record.purchase_ids:
                purchase = record.lot_id.purchase_order_ids
                purchase_line = purchase.order_line.filtered(lambda x: x.product_id == record.product_id)
                line_ids = purchase.order_line.filtered(
                    lambda x: x.product_id == record.product_id and x.price_unit > 0 and x.product_uom_qty > 0)
                record.purchase_ids = purchase.ids
                if len(line_ids) > 1:
                    # 判断单价是否一致
                    unit_list = set([round(i.price_unit, 2) for i in line_ids])
                    if len(unit_list) > 1:
                        # 对单价进行加权平均
                        total = 0
                        for line in line_ids:
                            total += round(line.price_unit * line.product_uom_qty, 4)
                        weighted_avg = round(total / sum(line_ids.mapped('product_uom_qty')), 4)
                        price_unit = weighted_avg
                        currency_id = line_ids[0].currency_id.id
                    else:
                        price_unit = line_ids[0].price_unit
                        currency_id = line_ids[0].currency_id.id
                else:
                    price_unit = line_ids.price_unit
                    currency_id = purchase[0].currency_id.id if purchase else False
            # 采购单位
            record.product_uom = product_uom
            record.s_product_uom = product_uom
            # 采购数量
            record.purchase_qty = record.uom_id._compute_quantity(record.quantity, record.product_uom)
            record.s_purchase_qty = record.uom_id._compute_quantity(record.quantity, record.product_uom, round=True)
            record.currency_id = currency_id
            supplier = record.purchase_ids.partner_id
            record.supplier_ids = supplier.ids
            record.supplier_code = ','.join([i.code or '' for i in supplier])
            record.s_supplier_name_en = ','.join([i.name_en or '' for i in supplier])
            record.taxes_ids = purchase_line.taxes_id.ids
            record.user_ids = record.purchase_ids.user_id.ids
            record.country_ids = record.supplier_ids.country_id.ids
            # 采购单价
            record.price_unit = round(price_unit, 4)
            if record.product_uom:
                # 库存单价
                record.price = record.product_uom._compute_price(record.price_unit, record.uom_id)
                # 采购总价
                record.price_total = record.price_unit * record.purchase_qty
                # 库存总价
                record.price_subtotal = record.price * record.quantity
            else:
                # 库存单价
                record.price = 0
                # 采购总价
                record.price_total = 0
                # 库存总价
                record.price_subtotal = 0

    @property
    def _table_query(self):
        """

        """
        # 成品,半成品分类
        category_ids = [self.env.ref('sb_sale.semi_product_type').id, self.env.ref('sb_sale.finished_product_type').id]
        c = self.env['product.category'].sudo().search([('code', '=', 'OPFP')])
        c |= c.child_id
        category_ids.extend(c.ids)
        to_date = self._context.get('to_date')
        from_date = self._context.get('from_date')
        query = f"""
        WITH
        -- 库存位置
        location1 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal')),
        location2 AS (SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal'),
        location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
        -- 生产前位置
        location4 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal')),
        location5 AS (SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal'),
        location6 AS (SELECT l4.id FROM location4 l4 UNION ALL SELECT l5.id FROM location5 l5 ORDER BY id asc),
        product AS (
			SELECT pp.id, pp.default_code, pt.categ_id, pc.name as category_name
			FROM product_product pp
			    LEFT JOIN product_template pt ON pp.product_tmpl_id=pt.id
			    LEFT JOIN product_category pc on pt.categ_id=pc.id
			WHERE
			    pp.default_code IS NOT NULL and
			    pc.id not in {tuple(category_ids)}
		),
        move_inout AS (
            -- 入库
            SELECT
                sm.reference,
                sml.id,
                sml.product_id,
                sml.picking_id,
                sml.location_id,
                sml.location_dest_id,
                sml.date,
                sml.lot_id,
                sml.quantity,
                'in' AS type
            FROM stock_move_line sml
            left join stock_move sm ON sml.move_id=sm.id
            WHERE
                sml.state='done' AND
                sml.date>='{from_date}' AND
                sml.date<='{to_date}' AND
                sml.location_dest_id IN (SELECT id FROM location3) AND
                sml.location_id not in (SELECT id FROM location3)
            UNION ALL 
            --出库
            SELECT
                sm.reference,
                sml.id,
                sml.product_id,
                sml.picking_id,
                sml.location_id,
                sml.location_dest_id,
                sml.date,
                sml.lot_id,
                -sml.quantity AS quantity,
				'out' AS type
            FROM stock_move_line sml
            left join stock_move sm ON sml.move_id=sm.id
            WHERE
                sml.state='done' AND
                sml.date>='{from_date}' AND
                sml.date<='{to_date}' AND
                sml.location_id IN (SELECT id FROM location3) AND
                sml.location_dest_id not in (SELECT id FROM location3)
        ),
        template_main AS (
            SELECT 
                mio.id AS move_id,
                mio.reference,
                pp.id as product_id,
                mio.picking_id,
                mio.location_id,
                mio.location_dest_id,
                mio.date,
                mio.lot_id,
                mio.quantity,
                mio.type,
                CASE
                    -- 出库
                    WHEN sli.usage='internal' AND slo.usage='supplier' THEN '采购退货'
                    WHEN sli.usage='internal' AND slo.usage='customer' THEN '销售出库'
                    WHEN sli.usage='internal' AND slo.id in (select id from location6) THEN '生产领料'
                    WHEN sli.usage='internal' AND slo.usage='inventory' THEN '物料申领'
                    -- 入库
                    WHEN sli.usage='supplier' AND slo.usage='internal' THEN '采购入库'
                    WHEN sli.usage='customer' AND slo.usage='internal' THEN '销售退货入库'
                    WHEN sli.usage='inventory' AND slo.usage='internal' THEN '物料申领退回'
                    WHEN sli.id in (select id from location6) AND slo.usage='internal' THEN '物料申领退回'
                ELSE NULL END AS bs_type
            FROM move_inout mio
                LEFT JOIN product pp ON mio.product_id=pp.id
                LEFT JOIN stock_location sli ON mio.location_id=sli.id
                LEFT JOIN stock_location slo ON mio.location_dest_id=slo.id
            WHERE pp.categ_id NOT IN {tuple(category_ids)}
            )
        SELECT 
            row_number() OVER() AS id,
            *
        FROM template_main
        """
        return query


class FinishedQuantReport(models.Model):
    _name = "finished.quant.report"
    _description = 'Finished Quant Report'
    _auto = False

    picking_id = fields.Many2one('stock.picking', string='Picking')
    reference = fields.Char(string='Reference', related='move_id.reference')
    picking_type_id = fields.Many2one('stock.picking.type', string='Picking Type')
    production_id = fields.Many2many('mrp.production', string='Production', compute='compute_extra_fields')
    move_id = fields.Many2one('stock.move', string='Move')
    move_line_id = fields.Many2one('stock.move.line', string='Move Line')
    product_id = fields.Many2one('product.product', string='Product')
    default_code = fields.Char(string='Product Code', related='product_id.default_code')
    location_id = fields.Many2one('stock.location', string='From')
    location_dest_id = fields.Many2one('stock.location', string='To')
    date = fields.Datetime(string='oper Date')
    lot_id = fields.Many2one('stock.lot', string='Lot')
    uom_id = fields.Many2one('uom.uom', string='UOM')  # 库存单位
    quantity = fields.Float(string='move quantity')
    sale_id = fields.Many2one('sale.order', string='Sale Order', compute='compute_extra_fields')
    sale_line_id = fields.Many2one('sale.order.line', string='Sale Order Line', compute='compute_extra_fields')
    price_unit = fields.Float('Price Unit', compute='compute_extra_fields')
    currency_id = fields.Many2one('res.currency', string='Currency', compute='compute_extra_fields')
    cny_currency_id = fields.Many2one('res.currency', string='Currency', compute='compute_extra_fields')
    subtotal = fields.Float('subtotal', compute='compute_extra_fields')
    cny_rate = fields.Float(string='CNY Rate', compute='compute_extra_fields')
    cny_subtotal = fields.Monetary('CNY Subtotal', compute='compute_extra_fields', currency_field='cny_currency_id')
    partner_id = fields.Many2one('res.partner', string='Customer', compute='compute_extra_fields')
    partner_code = fields.Char(string='Partner Code', compute='compute_extra_fields')
    categ_id = fields.Many2one('product.category', string='Category')
    pn = fields.Char(string='Pn', compute='compute_extra_fields')
    type = fields.Selection([('in', 'In'), ('out', 'Out')], string='Type')
    setA = fields.Float(string='SetA', compute='compute_extra_fields')
    bs_type = fields.Char(string='BS Type')

    @api.depends('product_id')
    def compute_extra_fields(self):
        """
        partner_id
        sale_id
        sale_line_id
        pn
        setA
        production_id
        """
        for record in self:
            domain = [('active', 'in', [True, False]),  # 归档也要显示
                      ('product_id', '=', record.product_id.id),
                      ('lot_producing_id', '=', record.lot_id.id)]
            if record.picking_type_id.barcode == 'WTT-DELIVERY':
                order_line = record.move_id.sale_line_id
                partner = order_line.order_id.partner_id
                record.production_id = order_line.order_id.mrp_production_ids
            else:
                record.production_id = record.move_line_id.production_id.ids
                order_line = record.production_id.s_sale_order_line_ids
                partner = order_line.order_id.partner_id
                if not record.production_id:
                    order_line = record.move_id.sale_line_id
                    partner = order_line.order_id.partner_id
                    record.production_id = order_line.order_id.mrp_production_ids
                if not record.production_id:
                    production_id = self.env['mrp.production'].search(domain, limit=1)
                    record.production_id = production_id.ids
                    order_line = production_id.s_sale_order_line_ids
                    partner = production_id.customer
            order_line = order_line[0] if order_line else order_line
            uom_ids = record.product_id.uom_value_ids
            set_length = uom_ids.filtered(lambda x: str(x.uom_id.name).upper() == "SetL".upper()).uom_value
            set_width = uom_ids.filtered(lambda x: str(x.uom_id.name).upper() == "SetW".upper()).uom_value
            pcs_set = uom_ids.filtered(lambda x: str(x.uom_id.name).upper() == "SET".upper()).uom_value
            sheet_are = 0
            if set_width and set_length:
                sheet_are = float(set_length) * float(set_width) / 1000000
            # 单个成品面积 * 数量
            quantity = record.quantity / pcs_set
            record.setA = sheet_are * quantity
            record.partner_id = partner.id
            record.partner_code = partner.code
            record.sale_id = order_line.order_id.id
            record.sale_line_id = order_line.id
            record.price_unit = order_line.price_unit
            record.pn = order_line.pn
            record.subtotal = order_line.price_unit * record.quantity
            # record.production_id = production_order.id
            # 转换成人民
            record.currency_id = order_line.currency_id
            cny_currency_id = self.env['res.currency'].search([('name', '=', 'CNY')], limit=1)
            record.cny_currency_id = cny_currency_id
            record.cny_rate = record.currency_id._get_conversion_rate(record.currency_id, cny_currency_id)
            record.cny_subtotal = record.cny_rate * record.subtotal

    def action_finished_report(self):
        from_date = self.env.context.get('from_date')
        to_date = self.env.context.get('to_date')
        sb_type = self.env.context.get('sb_type')
        return {
            'type': 'ir.actions.act_url',
            'url': f'/download/sb/finished/quant/excel?from_date={from_date}&to_date={to_date}&tp={sb_type}',
            'target': 'new',
        }

    @property
    def _table_query(self):
        finished_categ = self.env.ref('sb_sale.finished_product_type').id
        from_date = self._context.get('from_date')
        to_date = self._context.get('to_date')
        query = f"""
        WITH 
            -- 获取库存位置
            location1 AS (
            SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal')),
            location2 AS (SELECT id FROM stock_location WHERE barcode='WH-STOCK' AND usage='internal'),
            location3 AS (SELECT l1.id FROM location1 l1 UNION ALL SELECT l2.id FROM location2 l2 ORDER BY id asc),
            -- 生产前位置
            location4 AS (SELECT id FROM stock_location WHERE location_id=(SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal')),
            location5 AS (SELECT id FROM stock_location WHERE barcode='WH-PREPRODUCTION' AND usage='internal'),
            location6 AS (SELECT l4.id FROM location4 l4 UNION ALL SELECT l5.id FROM location5 l5 ORDER BY id asc),
            product_finish AS (
            SELECT pp.id, pt.name,pc.id as categ_id
            FROM product_product pp
                LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
                LEFT JOIN product_category pc on pt.categ_id=pc.id
            WHERE pc.id={finished_categ}
            ),
            move_line AS (
            -- 出
                SELECT 
                    sm.picking_type_id,
                    sm.picking_id,
                    sm.production_id,
                    sml.move_id,
                    sml.product_id,
                    sml.id as move_line_id,
                    sml.location_id,
                    sml.location_dest_id,
                    sml.date,
                    sml.lot_id,
                    sml.product_uom_id,
                    -sml.quantity as quantity,
                    'out' as type
                FROM stock_move_line sml
                    LEFT JOIN stock_move sm ON sm.id=sml.move_id
                    LEFT JOIN product_finish pf ON pf.id=sml.product_id
                WHERE 
                    sml.state='done' AND 
                    sml.date>='{from_date}' AND 
                    sml.date<='{to_date}' AND
                    sml.location_id in (SELECT id FROM location3) AND
                    sml.location_dest_id not in (SELECT id FROM location3) AND
                    pf.categ_id={finished_categ}
                UNION ALL
            -- 入
                SELECT 
                    sm.picking_type_id,
                    sm.picking_id,
                    sm.production_id,
                    sml.move_id,
                    sml.product_id,
                    sml.id as move_line_id,
                    sml.location_id,
                    sml.location_dest_id,
                    sml.date,
                    sml.lot_id,
                    sml.product_uom_id,
                    sml.quantity,
                    'in' as type
                FROM stock_move_line sml
                    LEFT JOIN stock_move sm ON sm.id=sml.move_id
                    LEFT JOIN product_finish pf ON pf.id=sml.product_id
                WHERE 
                    sml.state='done' AND 
                    sml.date>='{from_date}' AND 
                    sml.date<='{to_date}' AND
                    sml.location_dest_id in (SELECT id FROM location3) AND
                    sml.location_id not in (SELECT id FROM location3) AND
                    pf.categ_id={finished_categ}
            ),
            -- 连表操作取值
            main AS (
                SELECT
                    row_number() OVER() AS id,
                    ml.picking_type_id,
                    ml.picking_id,
                    ml.production_id,
                    ml.move_id,
                    ml.move_line_id,
                    ml.product_id,
                    ml.location_id,
                    ml.location_dest_id,
                    ml.date,
                    ml.lot_id,
                    ml.product_uom_id as uom_id,
                    ml.quantity,
                    pf.categ_id,
                    ml.type,
                    CASE
                        --出库
                        WHEN sli.usage='internal' AND slo.usage='supplier' THEN '采购退货'
                        WHEN sli.usage='internal' AND slo.usage='customer' THEN '销售出库'
                        WHEN sli.usage='internal' AND slo.id in (select id from location6) THEN '生产出库'
                        --入库
                        WHEN sli.usage='supplier' AND slo.usage='internal' THEN '采购入库'
                        WHEN sli.usage='customer' AND slo.usage='internal' THEN '销售退货'
                        WHEN sli.id in (select id from location6) AND slo.usage='internal' THEN '生产入库'
                    ELSE NULL END AS bs_type
                FROM move_line ml
                    LEFT JOIN product_finish pf ON pf.id=ml.product_id
                    LEFT JOIN stock_location sli ON ml.location_id=sli.id
                    LEFT JOIN stock_location slo ON ml.location_dest_id=slo.id
                WHERE pf.name is NOT NULL
            )
            SELECT * FROM main
        """
        return query


class FinishedProductionReport(models.Model):
    _name = 'finished.production.report'
    _description = '生产完成报表'
    _auto = False

    sale_order = fields.Char(string='销售单号')
    partner_name = fields.Char(string='客户名称')
    code = fields.Char(string='客户编码')
    product_id = fields.Many2one('product.product', string='产品')
    uom_id = fields.Many2one('uom.uom', string='单位', related='product_id.uom_id')
    pcs_a = fields.Float(string='PCS A', compute='_compute_base_mo', digits=(10, 6))
    pcs_a_total = fields.Float(string='总面积', compute='_compute_base_mo')
    base_mo = fields.Char(string='基本工单号')
    product_qty = fields.Float(string='投料数')
    qty_wip = fields.Float(string='在制数')
    qty_producing = fields.Float(string='产出数')
    qty_reject = fields.Float('生产报废')
    qty_income = fields.Float('入库数')
    qty_production = fields.Float('生产前')

    @api.depends('product_id')
    def _compute_base_mo(self):
        setl = self.env.ref('pcbmanufact.uom_set_length')
        setw = self.env.ref('pcbmanufact.uom_set_width')
        sheet = self.env.ref('pcbmanufact.uom_set')
        for record in self:
            uom_ids = record.product_id.uom_value_ids
            set_length = uom_ids.filtered(lambda x: x.uom_id.id == setl.id).uom_value
            set_width = uom_ids.filtered(lambda x: x.uom_id.id == setw.id).uom_value
            pcs_set = uom_ids.filtered(lambda x: x.uom_id.id == sheet.id).uom_value
            sheet_are = 0
            if set_width and set_length:
                sheet_are = float(set_length) * float(set_width) / 1000000
            if pcs_set > 0:
                pcs_a = sheet_are / pcs_set
            else:
                pcs_a = 0
            record.pcs_a = pcs_a
            record.pcs_a_total = pcs_a * record.qty_income

    @property
    def _table_query(self):
        from_date = self._context.get('from_date')
        to_date = self._context.get('to_date')
        query = f"""
with
    --基础MO
    base_mo as (
        --
        select distinct mp.base_mo,
                        mp.product_id,
                        pt.name,
                        mp.lot_producing_id as lot_id
        from mrp_production mp
                 left join product_product pp on pp.id = mp.product_id
                 left join product_template pt on pt.id = pp.product_tmpl_id
                 left join product_category pc on pc.id = pt.categ_id
        where pc.code in ('CHP', 'OPFP')),
    -- 生产前遗留数
    production_mo_line as (
        --
        select distinct sml.id,
                        bm.base_mo,
                        bm.lot_id,
                        sml.product_id,
                        -sml.quantity as quantity
        from stock_move_line sml
                 left join base_mo bm on bm.lot_id = sml.lot_id
                 left join stock_location sl on sl.id = sml.location_id
        where bm.base_mo is not null
          and sml.state = 'done'
          and sml.date > '{from_date}'
          and sml.date <= '{to_date}'
          and sl.id = 17
        union all
        select distinct sml.id,
                        bm.base_mo,
                        bm.lot_id,
                        sml.product_id,
                        sml.quantity as quantity
        from stock_move_line sml
                 left join base_mo bm on bm.lot_id = sml.lot_id
                 left join stock_location sl on sl.id = sml.location_dest_id
        where bm.base_mo is not null
          and sml.state = 'done'
          and sml.date > '{from_date}'
          and sml.date <= '{to_date}'
          and sl.id = 17),
    production_mo_product as (
        --
        select base_mo,
               product_id,
               sum(quantity) as quantity
        from production_mo_line
        group by base_mo, product_id),
    --投料明细
    base_mo_line as (
        --
        select distinct mp.id,
                        mp.base_mo,
                        mp.product_id,
                        mp.product_qty,
                        case
                            when mp.state <> 'done' then 0
                            else coalesce(mp.qty_producing, 0) - mp.qty_reject
                            end              as qty_producing,
                        mp.qty_reject,
                        CASE
                            when mp.date_finished > '{to_date}' then 0
                            WHEN mp.state = 'done' THEN coalesce(mp.qty_producing, 0) - coalesce(mp.qty_reject, 0)
                            WHEN mp.state <> 'done' THEN 0
                            END              as qty_income,
                        production_sale.sale_order,
                        production_sale.partner_name,
                        production_sale.code as code
        from mrp_production mp
                 left join base_mo bm on bm.base_mo = mp.base_mo
                 left join product_product pp on pp.id = mp.product_id
                 left join product_template pt on pt.id = pp.product_tmpl_id
                 left join product_category pc on pt.categ_id = pc.id
                 left join (select distinct mp.base_mo,
                                            array_to_string(array_agg(DISTINCT so.name), ',') as sale_order,
                                            rp.complete_name                                  as partner_name,
                                            rp.code
                            from mrp_production_sale_order_line_rel mplr
                                     join mrp_production mp on mplr.mrp_production_id = mp.id
                                     join sale_order_line sol on mplr.sale_order_line_id = sol.id
                                     join sale_order so on sol.order_id = so.id
                                     join res_partner rp on so.partner_id = rp.id
                            group by mp.base_mo, rp.complete_name, rp.code) production_sale
                           on bm.base_mo = production_sale.base_mo
        where pc.code in ('CHP', 'OPFP')
--and mp.active = True
          and mp.create_date > '{from_date}'
          and mp.create_date <= '{to_date}'
          and mp.state <> 'cancel'),
    -- 汇总base_mo投料数
    base_mo_product as (
        --
        select bm.base_mo,
               bm.product_id,
               pp.name                                                       as product_name,
               bm.sale_order,
               bm.partner_name,
               bm.code                                                       as code,
               sum(bm.product_qty)                                           as product_qty,
               sum(bm.product_qty) - sum(bm.qty_reject) - sum(bm.qty_income) as qty_wip,
               sum(bm.qty_producing)                                         as qty_producing,
               sum(bm.qty_reject)                                            as qty_reject,
               sum(bm.qty_income)                                            as qty_income
        from base_mo_line bm
                 left join (select pp.id, pt.name
                            from product_product pp
                                     left join product_template pt on pp.product_tmpl_id = pt.id) pp
                           on pp.id = bm.product_id
        group by bm.base_mo, bm.product_id, bm.sale_order, pp.name, bm.partner_name, bm.code),
    product_inventory_mo_line as (
        --
        SELECT sm.id,
               sml.production_id,
               sml.quantity      as QTY,
               CASE
                   WHEN sli.id = 14 and slo.usage = 'internal' THEN '调整数量+'
                   WHEN sli.usage = 'internal' and slo.usage = 'inventory' and slo.id = 14 THEN '调整数量-'
                   WHEN sli.usage = 'internal' and slo.id = 19 THEN '仓库报废(库存损失)'
                   ELSE '' END   as TRAN_DESC,
               p.base_mo,
               pp.name,
               sml.reference,
               sm.write_uid,
               sml.lot_id,
               sml.id,
               sp.origin,
               sml.lot_id,
               sml.location_dest_id,
               sml.location_id,
               sli.usage,
               slo.usage         as d_usage,
               sli.complete_name,
               slo.complete_name as d_name,
               sli.barcode,
               slo.barcode       as d_barcode,
               p.name            as production_name,
               sml.create_date
        FROM stock_move_line sml
                 join stock_move sm ON sml.move_id = sm.id
                 JOIN stock_location sli ON sml.location_id = sli.id
                 JOIN stock_location slo ON sml.location_dest_id = slo.id
                 left join mrp_production p on sm.production_id = p.id
                 left join (SELECT pp.id, pt.name, pc.id as categ_id
                            FROM product_product pp
                                     LEFT JOIN product_template pt on pt.id = pp.product_tmpl_id
                                     LEFT JOIN product_category pc on pt.categ_id = pc.id) pp
                           on pp.id = sml.product_id
                 left join stock_picking sp on sm.picking_id = sp.id

        WHERE sml.state = 'done'
          and pp.categ_id = 6
        ORDER BY SML.id)
select 
       row_number() OVER () AS id,
       bm.base_mo                               as base_mo,
       bm.product_id                            as product_id,
       bm.product_name                          as product_name,
       bm.sale_order                            as sale_order,
       bm.partner_name                          as partner_name,
       bm.code                                  as code,
       bm.product_qty                           as product_qty,
       bm.qty_wip                               as qty_wip,
       bm.qty_producing                         as qty_producing,
       bm.qty_reject                            as qty_reject,
       bm.qty_income - coalesce(pm.quantity, 0) as qty_income,
       coalesce(pm.quantity, 0)                 as qty_production
from base_mo_product bm
         left join production_mo_product pm on pm.base_mo = bm.base_mo and pm.product_id = bm.product_id
        """
        return query
